{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6dd61309",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea20dffa",
   "metadata": {},
   "source": [
    "# 原始数据导入与预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "35b5893a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 原始数据导入与预处理\n",
    "\n",
    "\n",
    "path=r'j:\\王振洋资料\\2.商贸分公司资料\\9月商贸分公司资料\\20240831代理跑男-讯领部分34450元（已开票）.xlsx'\n",
    "\n",
    "wbook1= xw.Book(path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "54eae2ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8月代理跑男发货明细</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>日期</td>\n",
       "      <td>城市名称</td>\n",
       "      <td>商品名称</td>\n",
       "      <td>尺码</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>数量</td>\n",
       "      <td>单价</td>\n",
       "      <td>金额</td>\n",
       "      <td>快递单号</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>L</td>\n",
       "      <td>XL</td>\n",
       "      <td>2XL</td>\n",
       "      <td>3XL</td>\n",
       "      <td>4XL</td>\n",
       "      <td>5XL</td>\n",
       "      <td>6XL</td>\n",
       "      <td>7XL</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-08-07 00:00:00</td>\n",
       "      <td>楚雄</td>\n",
       "      <td>冲锋衣（亲民版）</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>20.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>20.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>顺心捷达S35196795881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>80.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2080.0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    0     1         2     3     4     5     6     7     8   \\\n",
       "0           8月代理跑男发货明细  None      None  None  None  None  None  None  None   \n",
       "1                   日期  城市名称      商品名称    尺码  None  None  None  None  None   \n",
       "2                 None  None      None     L    XL   2XL   3XL   4XL   5XL   \n",
       "3  2024-08-07 00:00:00    楚雄  冲锋衣（亲民版）  None  None  None  20.0  None  None   \n",
       "4                 None  None    头盔（夏季）  None  None  None  None  None  None   \n",
       "\n",
       "     9     10    11    12      13                14  \n",
       "0  None  None  None  None    None              None  \n",
       "1  None  None    数量    单价      金额              快递单号  \n",
       "2   6XL   7XL  None  None    None              None  \n",
       "3  None  None  20.0  66.0  1320.0  顺心捷达S35196795881  \n",
       "4  None  None  80.0  26.0  2080.0              None  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入数据并读取到dataframe\n",
    "原始数据源sheet='供应商发货明细'\n",
    "\n",
    "\n",
    "df1=wbook1.sheets(原始数据源sheet).range(\"a1\").current_region.options(pd.DataFrame,header=False,index=False).value\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1d03e4d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 完善 index、column、填充缺失值等清理数据\n",
    "\n",
    "df1.columns=df1.iloc[1]  # 将第二行用作标题\n",
    "\n",
    "df2=df1[2:]  #从第4行开始读取数据，等于删除了0,1,2，三行\n",
    "df2.loc[:,'日期']=df2['日期'].fillna(method='ffill') # 填充日期列\n",
    "df2.loc[:,'日期']=df2['日期'].astype('str').str.replace('00:00:00','')\n",
    "df2.loc[:,'城市名称']=df2['城市名称'].fillna(method='ffill')  #填充城市名称列\n",
    "\n",
    "\n",
    "df2=df2.loc[:,['日期','城市名称','商品名称','数量','金额']]\n",
    "\n",
    "df2.head()\n",
    "df2.to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f10afb38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>1</th>\n",
       "      <th>城市名称</th>\n",
       "      <th>商品名称</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>东光</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>5.0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>东光</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>5.0</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>东光</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>5.0</td>\n",
       "      <td>82.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>会东</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>兴义</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>540.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "1 城市名称        商品名称    数量     金额\n",
       "0   东光  保温箱（普通版）加大   5.0  270.0\n",
       "1   东光      头盔（夏季）   5.0  130.0\n",
       "2   东光     马甲（普通版）   5.0   82.5\n",
       "3   会东     马甲（普通版）  10.0  165.0\n",
       "4   兴义    保温箱（普通版）  10.0  540.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3=df2.groupby(by=[\"城市名称\",\"商品名称\"])[[\"数量\",\"金额\"]].sum().reset_index()\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "74d16f21",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 写入到工作表\n",
    "\n",
    "wbook1.sheets('编码匹配中间表').cells.clear()\n",
    "wbook1.sheets('编码匹配中间表').cells.number_format = '@' # 将单元格的数据格式设置为文本\n",
    "wbook1.sheets('编码匹配中间表').range('a1').value=df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "652ad4d7",
   "metadata": {},
   "source": [
    "# 编码匹配中间表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90af35d1",
   "metadata": {},
   "source": [
    "## 匹配仓库、部门、客户、存货编码、科目 等 各项内容\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "84eba7bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开t+基础数据表并写入相关数据，为匹配做准备\n",
    "wb基础数据=xw.Book(r'J:\\王振洋资料\\2.商贸分公司资料\\t+基础数据金水分.xlsx')\n",
    "往来存货档案=wb基础数据.sheets('往来单位匹配汇总').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "441a30ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "合并=pd.merge(df3,往来存货档案,left_on='城市名称',right_on='城市',how='left')\n",
    "合并=pd.merge(合并,存货档案,left_on='商品名称',right_on='存货名称',how='left')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "99603a77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>城市名称</th>\n",
       "      <th>商品名称</th>\n",
       "      <th>数量</th>\n",
       "      <th>金额</th>\n",
       "      <th>城市</th>\n",
       "      <th>往来单位编码</th>\n",
       "      <th>往来单位名称</th>\n",
       "      <th>仓库编码</th>\n",
       "      <th>仓库名称</th>\n",
       "      <th>存货编码</th>\n",
       "      <th>存货名称</th>\n",
       "      <th>计量单位</th>\n",
       "      <th>收入科目编码</th>\n",
       "      <th>收入科目名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>东光</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>5.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>东光</td>\n",
       "      <td>S001251</td>\n",
       "      <td>东光代理商</td>\n",
       "      <td>242</td>\n",
       "      <td>东光代理商</td>\n",
       "      <td>010213</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>个</td>\n",
       "      <td>50010213</td>\n",
       "      <td>保温箱（普通版）加大收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>东光</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>5.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>东光</td>\n",
       "      <td>S001251</td>\n",
       "      <td>东光代理商</td>\n",
       "      <td>242</td>\n",
       "      <td>东光代理商</td>\n",
       "      <td>010204</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010204</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>东光</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>5.0</td>\n",
       "      <td>82.5</td>\n",
       "      <td>东光</td>\n",
       "      <td>S001251</td>\n",
       "      <td>东光代理商</td>\n",
       "      <td>242</td>\n",
       "      <td>东光代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>会东</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>会东</td>\n",
       "      <td>S001248</td>\n",
       "      <td>会东代理商</td>\n",
       "      <td>239.0</td>\n",
       "      <td>会东代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>兴义</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>540.0</td>\n",
       "      <td>兴义</td>\n",
       "      <td>S001015</td>\n",
       "      <td>兴义代理商</td>\n",
       "      <td>006</td>\n",
       "      <td>兴义代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>兴义</td>\n",
       "      <td>马甲（尊享版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>兴义</td>\n",
       "      <td>S001015</td>\n",
       "      <td>兴义代理商</td>\n",
       "      <td>006</td>\n",
       "      <td>兴义代理商</td>\n",
       "      <td>010207</td>\n",
       "      <td>马甲（尊享版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010207</td>\n",
       "      <td>马甲（尊享版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>南阳</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1620.0</td>\n",
       "      <td>南阳</td>\n",
       "      <td>S001027</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>084</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>南阳</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>5.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>南阳</td>\n",
       "      <td>S001027</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>084</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>010213</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>个</td>\n",
       "      <td>50010213</td>\n",
       "      <td>保温箱（普通版）加大收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>南阳</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>780.0</td>\n",
       "      <td>南阳</td>\n",
       "      <td>S001027</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>084</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>010204</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010204</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>南阳</td>\n",
       "      <td>短袖</td>\n",
       "      <td>30.0</td>\n",
       "      <td>750.0</td>\n",
       "      <td>南阳</td>\n",
       "      <td>S001027</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>084</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>南阳</td>\n",
       "      <td>雨衣（分体）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1830.0</td>\n",
       "      <td>南阳</td>\n",
       "      <td>S001027</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>084</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>010210</td>\n",
       "      <td>雨衣（分体）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010210</td>\n",
       "      <td>雨衣（分体）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>南阳</td>\n",
       "      <td>雨衣（连体）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1800.0</td>\n",
       "      <td>南阳</td>\n",
       "      <td>S001027</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>084</td>\n",
       "      <td>南阳代理商</td>\n",
       "      <td>010215</td>\n",
       "      <td>雨衣（连体）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010215</td>\n",
       "      <td>雨衣（连体）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>宿州</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1350.0</td>\n",
       "      <td>宿州</td>\n",
       "      <td>S001258</td>\n",
       "      <td>宿州代理商</td>\n",
       "      <td>249.0</td>\n",
       "      <td>宿州代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>宿州</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>25.0</td>\n",
       "      <td>412.5</td>\n",
       "      <td>宿州</td>\n",
       "      <td>S001258</td>\n",
       "      <td>宿州代理商</td>\n",
       "      <td>249.0</td>\n",
       "      <td>宿州代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>怒江</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>540.0</td>\n",
       "      <td>怒江</td>\n",
       "      <td>S001215</td>\n",
       "      <td>怒江代理商</td>\n",
       "      <td>184</td>\n",
       "      <td>怒江代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>怒江</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>怒江</td>\n",
       "      <td>S001215</td>\n",
       "      <td>怒江代理商</td>\n",
       "      <td>184</td>\n",
       "      <td>怒江代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>新沂</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>1.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>新沂</td>\n",
       "      <td>S001118</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>051</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>010213</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>个</td>\n",
       "      <td>50010213</td>\n",
       "      <td>保温箱（普通版）加大收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>新沂</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>新沂</td>\n",
       "      <td>S001118</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>051</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>010204</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010204</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>新沂</td>\n",
       "      <td>短袖</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>新沂</td>\n",
       "      <td>S001118</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>051</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>新沂</td>\n",
       "      <td>马甲（尊享版）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>新沂</td>\n",
       "      <td>S001118</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>051</td>\n",
       "      <td>新沂代理商</td>\n",
       "      <td>010207</td>\n",
       "      <td>马甲（尊享版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010207</td>\n",
       "      <td>马甲（尊享版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>晋城</td>\n",
       "      <td>雨衣（分体）</td>\n",
       "      <td>60.0</td>\n",
       "      <td>3660.0</td>\n",
       "      <td>晋城</td>\n",
       "      <td>S001002</td>\n",
       "      <td>晋城代理商</td>\n",
       "      <td>026</td>\n",
       "      <td>晋城代理商</td>\n",
       "      <td>010210</td>\n",
       "      <td>雨衣（分体）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010210</td>\n",
       "      <td>雨衣（分体）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>晋城</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>70.0</td>\n",
       "      <td>1155.0</td>\n",
       "      <td>晋城</td>\n",
       "      <td>S001002</td>\n",
       "      <td>晋城代理商</td>\n",
       "      <td>026</td>\n",
       "      <td>晋城代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>楚雄</td>\n",
       "      <td>冲锋衣（亲民版）</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>楚雄</td>\n",
       "      <td>S001075</td>\n",
       "      <td>楚雄代理商</td>\n",
       "      <td>014</td>\n",
       "      <td>楚雄代理商</td>\n",
       "      <td>010206</td>\n",
       "      <td>冲锋衣（亲民版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010206</td>\n",
       "      <td>冲锋衣（亲民版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>楚雄</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2080.0</td>\n",
       "      <td>楚雄</td>\n",
       "      <td>S001075</td>\n",
       "      <td>楚雄代理商</td>\n",
       "      <td>014</td>\n",
       "      <td>楚雄代理商</td>\n",
       "      <td>010204</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010204</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>洛阳</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>50.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>洛阳</td>\n",
       "      <td>S001024</td>\n",
       "      <td>洛阳代理商</td>\n",
       "      <td>011</td>\n",
       "      <td>洛阳代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>洛阳</td>\n",
       "      <td>冲锋衣（亲民版）</td>\n",
       "      <td>60.0</td>\n",
       "      <td>3960.0</td>\n",
       "      <td>洛阳</td>\n",
       "      <td>S001024</td>\n",
       "      <td>洛阳代理商</td>\n",
       "      <td>011</td>\n",
       "      <td>洛阳代理商</td>\n",
       "      <td>010206</td>\n",
       "      <td>冲锋衣（亲民版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010206</td>\n",
       "      <td>冲锋衣（亲民版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>红河</td>\n",
       "      <td>短袖</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>-400.0</td>\n",
       "      <td>红河</td>\n",
       "      <td>S001045</td>\n",
       "      <td>红河代理商</td>\n",
       "      <td>018</td>\n",
       "      <td>红河代理商</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>芒市</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1620.0</td>\n",
       "      <td>芒市</td>\n",
       "      <td>S001109</td>\n",
       "      <td>芒市代理商</td>\n",
       "      <td>044</td>\n",
       "      <td>芒市代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>芒市</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>780.0</td>\n",
       "      <td>芒市</td>\n",
       "      <td>S001109</td>\n",
       "      <td>芒市代理商</td>\n",
       "      <td>044</td>\n",
       "      <td>芒市代理商</td>\n",
       "      <td>010204</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010204</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>芒市</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>30.0</td>\n",
       "      <td>495.0</td>\n",
       "      <td>芒市</td>\n",
       "      <td>S001109</td>\n",
       "      <td>芒市代理商</td>\n",
       "      <td>044</td>\n",
       "      <td>芒市代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>阜阳</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5400.0</td>\n",
       "      <td>阜阳</td>\n",
       "      <td>S001069</td>\n",
       "      <td>阜阳代理商</td>\n",
       "      <td>147</td>\n",
       "      <td>阜阳代理商</td>\n",
       "      <td>010205</td>\n",
       "      <td>保温箱（普通版）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010205</td>\n",
       "      <td>保温箱（普通版）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>霸州</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>5.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>霸州</td>\n",
       "      <td>S001018</td>\n",
       "      <td>霸州代理商</td>\n",
       "      <td>034</td>\n",
       "      <td>霸州代理商</td>\n",
       "      <td>010213</td>\n",
       "      <td>保温箱（普通版）加大</td>\n",
       "      <td>个</td>\n",
       "      <td>50010213</td>\n",
       "      <td>保温箱（普通版）加大收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>霸州</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>7.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>霸州</td>\n",
       "      <td>S001018</td>\n",
       "      <td>霸州代理商</td>\n",
       "      <td>034</td>\n",
       "      <td>霸州代理商</td>\n",
       "      <td>010204</td>\n",
       "      <td>头盔（夏季）</td>\n",
       "      <td>个</td>\n",
       "      <td>50010204</td>\n",
       "      <td>头盔（夏季）收入</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>霸州</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>霸州</td>\n",
       "      <td>S001018</td>\n",
       "      <td>霸州代理商</td>\n",
       "      <td>034</td>\n",
       "      <td>霸州代理商</td>\n",
       "      <td>010201</td>\n",
       "      <td>马甲（普通版）</td>\n",
       "      <td>件</td>\n",
       "      <td>50010201</td>\n",
       "      <td>马甲（普通版）收入</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   城市名称        商品名称     数量      金额  城市   往来单位编码 往来单位名称   仓库编码   仓库名称    存货编码  \\\n",
       "0    东光  保温箱（普通版）加大    5.0   270.0  东光  S001251  东光代理商    242  东光代理商  010213   \n",
       "1    东光      头盔（夏季）    5.0   130.0  东光  S001251  东光代理商    242  东光代理商  010204   \n",
       "2    东光     马甲（普通版）    5.0    82.5  东光  S001251  东光代理商    242  东光代理商  010201   \n",
       "3    会东     马甲（普通版）   10.0   165.0  会东  S001248  会东代理商  239.0  会东代理商  010201   \n",
       "4    兴义    保温箱（普通版）   10.0   540.0  兴义  S001015  兴义代理商    006  兴义代理商  010205   \n",
       "5    兴义     马甲（尊享版）   10.0   230.0  兴义  S001015  兴义代理商    006  兴义代理商  010207   \n",
       "6    南阳    保温箱（普通版）   30.0  1620.0  南阳  S001027  南阳代理商    084  南阳代理商  010205   \n",
       "7    南阳  保温箱（普通版）加大    5.0   270.0  南阳  S001027  南阳代理商    084  南阳代理商  010213   \n",
       "8    南阳      头盔（夏季）   30.0   780.0  南阳  S001027  南阳代理商    084  南阳代理商  010204   \n",
       "9    南阳          短袖   30.0   750.0  南阳  S001027  南阳代理商    084  南阳代理商     NaN   \n",
       "10   南阳      雨衣（分体）   30.0  1830.0  南阳  S001027  南阳代理商    084  南阳代理商  010210   \n",
       "11   南阳      雨衣（连体）   30.0  1800.0  南阳  S001027  南阳代理商    084  南阳代理商  010215   \n",
       "12   宿州    保温箱（普通版）   25.0  1350.0  宿州  S001258  宿州代理商  249.0  宿州代理商  010205   \n",
       "13   宿州     马甲（普通版）   25.0   412.5  宿州  S001258  宿州代理商  249.0  宿州代理商  010201   \n",
       "14   怒江    保温箱（普通版）   10.0   540.0  怒江  S001215  怒江代理商    184  怒江代理商  010205   \n",
       "15   怒江     马甲（普通版）   10.0   165.0  怒江  S001215  怒江代理商    184  怒江代理商  010201   \n",
       "16   新沂  保温箱（普通版）加大    1.0    54.0  新沂  S001118  新沂代理商    051  新沂代理商  010213   \n",
       "17   新沂      头盔（夏季）    1.0    26.0  新沂  S001118  新沂代理商    051  新沂代理商  010204   \n",
       "18   新沂          短袖    1.0    25.0  新沂  S001118  新沂代理商    051  新沂代理商     NaN   \n",
       "19   新沂     马甲（尊享版）    1.0    23.0  新沂  S001118  新沂代理商    051  新沂代理商  010207   \n",
       "20   晋城      雨衣（分体）   60.0  3660.0  晋城  S001002  晋城代理商    026  晋城代理商  010210   \n",
       "21   晋城     马甲（普通版）   70.0  1155.0  晋城  S001002  晋城代理商    026  晋城代理商  010201   \n",
       "22   楚雄    冲锋衣（亲民版）   20.0  1320.0  楚雄  S001075  楚雄代理商    014  楚雄代理商  010206   \n",
       "23   楚雄      头盔（夏季）   80.0  2080.0  楚雄  S001075  楚雄代理商    014  楚雄代理商  010204   \n",
       "24   洛阳    保温箱（普通版）   50.0  2700.0  洛阳  S001024  洛阳代理商    011  洛阳代理商  010205   \n",
       "25   洛阳    冲锋衣（亲民版）   60.0  3960.0  洛阳  S001024  洛阳代理商    011  洛阳代理商  010206   \n",
       "26   红河          短袖  -16.0  -400.0  红河  S001045  红河代理商    018  红河代理商     NaN   \n",
       "27   芒市    保温箱（普通版）   30.0  1620.0  芒市  S001109  芒市代理商    044  芒市代理商  010205   \n",
       "28   芒市      头盔（夏季）   30.0   780.0  芒市  S001109  芒市代理商    044  芒市代理商  010204   \n",
       "29   芒市     马甲（普通版）   30.0   495.0  芒市  S001109  芒市代理商    044  芒市代理商  010201   \n",
       "30   阜阳    保温箱（普通版）  100.0  5400.0  阜阳  S001069  阜阳代理商    147  阜阳代理商  010205   \n",
       "31   霸州  保温箱（普通版）加大    5.0   270.0  霸州  S001018  霸州代理商    034  霸州代理商  010213   \n",
       "32   霸州      头盔（夏季）    7.0   182.0  霸州  S001018  霸州代理商    034  霸州代理商  010204   \n",
       "33   霸州     马甲（普通版）   10.0   165.0  霸州  S001018  霸州代理商    034  霸州代理商  010201   \n",
       "\n",
       "          存货名称 计量单位    收入科目编码        收入科目名称  \n",
       "0   保温箱（普通版）加大    个  50010213  保温箱（普通版）加大收入  \n",
       "1       头盔（夏季）    个  50010204      头盔（夏季）收入  \n",
       "2      马甲（普通版）    件  50010201     马甲（普通版）收入  \n",
       "3      马甲（普通版）    件  50010201     马甲（普通版）收入  \n",
       "4     保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "5      马甲（尊享版）    件  50010207     马甲（尊享版）收入  \n",
       "6     保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "7   保温箱（普通版）加大    个  50010213  保温箱（普通版）加大收入  \n",
       "8       头盔（夏季）    个  50010204      头盔（夏季）收入  \n",
       "9          NaN  NaN       NaN           NaN  \n",
       "10      雨衣（分体）    件  50010210      雨衣（分体）收入  \n",
       "11      雨衣（连体）    件  50010215      雨衣（连体）收入  \n",
       "12    保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "13     马甲（普通版）    件  50010201     马甲（普通版）收入  \n",
       "14    保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "15     马甲（普通版）    件  50010201     马甲（普通版）收入  \n",
       "16  保温箱（普通版）加大    个  50010213  保温箱（普通版）加大收入  \n",
       "17      头盔（夏季）    个  50010204      头盔（夏季）收入  \n",
       "18         NaN  NaN       NaN           NaN  \n",
       "19     马甲（尊享版）    件  50010207     马甲（尊享版）收入  \n",
       "20      雨衣（分体）    件  50010210      雨衣（分体）收入  \n",
       "21     马甲（普通版）    件  50010201     马甲（普通版）收入  \n",
       "22    冲锋衣（亲民版）    件  50010206    冲锋衣（亲民版）收入  \n",
       "23      头盔（夏季）    个  50010204      头盔（夏季）收入  \n",
       "24    保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "25    冲锋衣（亲民版）    件  50010206    冲锋衣（亲民版）收入  \n",
       "26         NaN  NaN       NaN           NaN  \n",
       "27    保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "28      头盔（夏季）    个  50010204      头盔（夏季）收入  \n",
       "29     马甲（普通版）    件  50010201     马甲（普通版）收入  \n",
       "30    保温箱（普通版）    个  50010205    保温箱（普通版）收入  \n",
       "31  保温箱（普通版）加大    个  50010213  保温箱（普通版）加大收入  \n",
       "32      头盔（夏季）    个  50010204      头盔（夏季）收入  \n",
       "33     马甲（普通版）    件  50010201     马甲（普通版）收入  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "合并\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8932541",
   "metadata": {},
   "source": [
    "## 将数据写入到编码匹配中间表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3858e303",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将合并写入<编码匹配中间表>\n",
    "\n",
    "# 创建一个sheet名字构建的列表，用于后续判断\n",
    "sheet_name=[wbook1.sheets[i].name for i in range(wbook1.sheets.count)]\n",
    "# 将数据写入到编码匹配中间表，对匹配失败的数据手动补充\n",
    "if '编码匹配中间表' in sheet_name:\n",
    "    ws=wbook1.sheets('编码匹配中间表')\n",
    "    ws.cells.clear()\n",
    "    ws.cells.number_format = '@'\n",
    "    ws.range('a1').value=合并\n",
    "else:\n",
    "    new_sheet=wbook1.sheets.add('编码匹配中间表')\n",
    "    new_sheet.cells.number_format = '@'\n",
    "    new_sheet.range('a1').value=合并\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03633af2",
   "metadata": {},
   "source": [
    "# 手动在编码匹配中间表中补充相关数据\n",
    "1. 未能匹配到的数据\n",
    "2. 添加部门信息，除郑州的家政物料外，其余的部门均为招商部\n",
    "3. 添加供应商信息，根据实际的供应商添加"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90455bcc",
   "metadata": {},
   "source": [
    "# 数据从编码匹配中间表--->模版数据源表\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "29763780",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 压缩数据\n",
    "# 将读取后的数据 进行压缩、汇总、透视，并生成备注列\n",
    "\n",
    "df1=wbook1.sheets('编码匹配中间表').range('a1').expand().options(pd.DataFrame).value\n",
    "\n",
    "df2=df1.groupby(by=['部门编码','部门','仓库编码','仓库名称','存货编码','存货名称','计量单位','供应商编码','供应商'])[['数量','金额']].sum().reset_index()\n",
    "\n",
    "df2['城市2']=df2['仓库名称'].str.replace('代理商','')  #将代理商三个字符替代为空\n",
    "\n",
    "df2['数量2']=df2['数量'].astype(str).str.replace('.0','')  # 新增一个数量2列，转化为文本并替换其中的.0，方便后续做文本链接\n",
    "\n",
    "df2['备注'] = df2['城市2'] + '购入'  + df2['数量2'] + df2['存货名称']  #增加一个备注列\n",
    "\n",
    "\n",
    "df2=df2.drop('数量2',axis=1)     #删除数量2列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "e526c648",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加汇总备注的函数，有了这个之后，备注可以按城市共用一个.\n",
    "def 添加汇总备注(x):\n",
    "    x['备注2'] = '+'.join(x['备注'].values)\n",
    "    x=x.drop('备注',axis=1)\n",
    "    return x\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "68a27ac1",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df3=df2.groupby(by=['城市2']).apply(添加汇总备注)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3321b247",
   "metadata": {},
   "source": [
    "# 模版数据源表\n",
    "在编码匹配中间表补充好数据的编码、科目等数据信息等后，就可以将数据粘贴到模版数据源表，并进一步补充“不含税金额”，“税额”，“往来单位等”。补充完成后，以模版数据源为基础，生成销售出库单和凭证导入导出表中的数据\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3e47f7a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "wbook1.sheets('模版数据源').cells.number_format='@'\n",
    "wbook1.sheets('模版数据源').range('a1').value=df3.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6145e720",
   "metadata": {},
   "source": [
    "# 创建采购入库单模版"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "75420d91",
   "metadata": {},
   "outputs": [],
   "source": [
    "入库单模版Paht=R'J:\\王振洋资料\\2.商贸分公司资料\\商贸分导入模板\\采购入库单.xlsx'\n",
    "\n",
    "sheetname = wbook1.sheet_names\n",
    "\n",
    "if '采购入库单' not in sheetname:\n",
    "    入库单=xw.Book(入库单模版Paht)\n",
    "    入库单.sheets['采购入库单'].copy(after=wbook1.sheets[wbook1.sheets.count-1])\n",
    "    入库单.close()\n",
    "else:\n",
    "    None\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "697b2edc",
   "metadata": {},
   "source": [
    "## 数据写入到采购入库单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3664fc73",
   "metadata": {},
   "outputs": [],
   "source": [
    "ws2=wbook1.sheets('采购入库单')\n",
    "ws3=wbook1.sheets('模版数据源')\n",
    "df4=ws3.range('a1').expand().options(pd.DataFrame).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "67051f2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "ws2.range('a5:aa10000').clear_contents()\n",
    "ws2.range('g3:j3').value=df4.loc[:,['供应商编码','供应商','部门编码','部门']].values\n",
    "ws2.range('m3:o3').value=df4.loc[:,['备注2','仓库编码','仓库名称']].values\n",
    "ws2.range('r3:s3').value=df4.loc[:,['存货编码','存货名称']].values\n",
    "ws2.range('u3:v3').value=df4.loc[:,['计量单位','数量']].values\n",
    "ws2.range('Y3').value=0.00\n",
    "ws2.range('z3').options(transpose=True).value=df4['金额'].values\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
